Performance and Tuning Series: Locking and Concurrency Control

SAP ® Adaptive Server ® Enterprise is a high-performance relational database management system for mission-critical, data-intensive environments. It ensures highest operational efficiency and throughput on a broad range of platforms. Configuring locks in SAP ASE includes understanding locking and data consistency, the granularity of locks, types of locks, lock compatibility, and how isolation levels affect locking. SAP ® Adaptive Server ® Enterprise (SAP ® ASE) protects the tables, data pages, or data rows used by active transactions by locking them. Locking is a concurrency control mechanism: it ensures the consistency of data within and across transactions. Locking is necessary in a multiuser environment, since several users may be working with the same data at the same time. Locking affects performance when one process holds locks that prevent another process from accessing needed data. This is called lock contention. The process that is blocked by the lock sleeps until the lock is released. A more serious impact on performance arises from deadlocks. A deadlock occurs when two user processes each have a lock on a page, row, or table and each process wants to acquire a lock on the page, row, or table held by the other. The transaction with the least accumulated CPU time is killed and all of its work is rolled back. Understanding the types of locks in SAP ASE can help to reduce lock contention and avoid or minimize deadlocks. Data consistency means that if multiple users repeatedly execute a series of transactions, the results are correct for each transaction, each time. Simultaneous retrievals and modifications of data do not interfere with each other: the results of queries are consistent. In this table example, transactions T1 and T2 are attempting to access data at approximately the same time. T1 is updating values in a column, while T2 needs to report the sum of the values. T1 Event Sequence T2 begin transaction update account set balance = balance-100 where acct_number = 25 update account set balance = balance + 100 where acct_number = 45 commit transaction T1 and T2 start. T1 up­ dates balance for one account by sub­ tracting $100. T2 queries the sum bal­ ance, which is off by $100 at this point in time—should it return results now, or wait until T1 ends? T1 up­ dates balance of the other account by add­ ing the $100. T1 ends. begin transaction select sum(balance) from account where …